/*
 * @(#)CommonSqlUtil.java
 *
 * Copyright 2012 by ShenY BIS, All rights reserved.
 */

package com.bis.framework.util.common;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.dom4j.DocumentException;
import org.dom4j.Element;

import com.bis.framework.annotation.TableAnnotation;
import com.bis.framework.base.entity.BaseEntity;
import com.bis.framework.security.context.SecurityContextInfo;
import com.bis.framework.util.date.DateUtils;
import com.bis.framework.util.xmlConvert.XmlDomUtil;

/**
 * CommonSqlUtil
 * 
 * @author BIS 2012-12-24
 */
public class CommonSqlUtil
{
    /**
     * Logger
     */
    private static final Logger logger = Logger.getLogger(CommonSqlUtil.class);
    /**
     * lstCondition
     */
    private List<Condition>     lstCondition;
    /**
     * lstUpdateFields
     */
    private List<String>        lstUpdateFields;

    /**
     * CommonSqlUtil
     */
    public CommonSqlUtil()
    {
        lstCondition = new LinkedList<Condition>();
        lstUpdateFields = new LinkedList<String>();
    }

    /**
     * appendUpdateField
     * @Description:
     * @param field
     */
    public void appendUpdateField(String field)
    {
        lstUpdateFields.add(field);
    }

    /**
     * removeUpdateField
     * @Description:
     * @param field
     */
    public void removeUpdateField(String field)
    {
        lstUpdateFields.remove(field);
    }

    /**
     * appendCondition
     * @Description:
     * @param condition
     */
    public void appendCondition(Condition condition)
    {
        lstCondition.add(condition);
    }

    /**
     * removeCondition
     * @Description:
     * @param condition
     */
    public void removeCondition(Condition condition)
    {
        lstCondition.remove(condition);
    }

    /**
     * buildUpdateSql
     * @Description:
     * @param sourceEntity
     * @return
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public String buildUpdateSql(BaseEntity sourceEntity) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, NoSuchMethodException
    {
        if (null == sourceEntity)
        {
            return null;
        }
        if (null != lstUpdateFields && lstUpdateFields.size() > 0)
        {
            StringBuffer sbSql = new StringBuffer("UPDATE ");
            TableAnnotation tableAnnotation = sourceEntity.getClass().getAnnotation(TableAnnotation.class);
            logger.debug("table name: " + tableAnnotation.table());
            sbSql.append(tableAnnotation.table()).append(" SET ");
            String tableName = tableAnnotation.table();
            for (String fieldName : lstUpdateFields)
            {
                Object fieldValue = sourceEntity.getClass().getMethod("get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1)).invoke(sourceEntity);
                Object sqlValue = getSqlValueByType(tableName, fieldName, fieldValue);
                //Object sqlValue = getSqlValueByType(fieldValue);
                sbSql.append(fieldName).append(" = ").append(sqlValue).append(",");
            }
            sbSql.replace(sbSql.lastIndexOf(","), sbSql.lastIndexOf(",") + 1, " ");
            String strCondition = getConditionSQL();
            if (null != strCondition)
            {
                sbSql.append(strCondition);
            }
            return sbSql.toString();
        }
        return null;
    }

    /**
     * buildUpdateSqlForAllProperties
     * @Description:
     * @param sourceEntity
     * @return
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public static String buildUpdateSqlForAllProperties(BaseEntity sourceEntity) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, NoSuchMethodException
    {
        if (null == sourceEntity)
        {
            return null;
        }
        StringBuffer sbSql = new StringBuffer("UPDATE ");
        TableAnnotation tableAnnotation = sourceEntity.getClass().getAnnotation(TableAnnotation.class);
        logger.debug("table name: " + tableAnnotation.table());
        sbSql.append(tableAnnotation.table()).append(" SET ");
        Field[] classField = sourceEntity.getClass().getDeclaredFields();
        String tableName = tableAnnotation.table();
        StringBuffer sbWhere = new StringBuffer(" 1=1 ");
        Field idField = null;
        for (int i = 0; classField != null && i < classField.length; i++)
        {
            Field field = classField[i];
            field.setAccessible(true);
            TableAnnotation columnAnnotation = field.getAnnotation(TableAnnotation.class);
            if (null != columnAnnotation && columnAnnotation.isForUpdate())
            {
                if (columnAnnotation.isCode())
                {
                    idField = field;
                    sbWhere.append(" and ").append(idField.getName()).append(" = ").append(getSqlValueByType(tableName, idField.getName(), idField.get(sourceEntity)));
                }
                Object fieldValue = field.get(sourceEntity);

                //2011-03-10 
                //Bean中有值，删除后保存无效，取消空串判断，只保留null判断
                //if (null != fieldValue && !"".equals(fieldValue))
                if (null != fieldValue)
                {
                    Object sqlValue = getSqlValueByType(tableName, field.getName(), fieldValue);
                    //Object sqlValue = getSqlValueByType(fieldValue);
                    sbSql.append(field.getName()).append(" = ").append(sqlValue).append(",");
                }
            }
        }
        sbSql.replace(sbSql.lastIndexOf(","), sbSql.lastIndexOf(",") + 1, " ");
        //sbSql.append(" WHERE id= ").append("'").append(sourceEntity.getClass().getMethod("getId").invoke(sourceEntity)).append("'");

        sbSql.append(" WHERE ").append(sbWhere);

        logger.debug(sbSql.toString());
        return sbSql.toString();
    }

    /**
     * getConditionSQL
     * @Description:
     * @return
     */
    private String getConditionSQL()
    {
        if (null != lstCondition && lstCondition.size() > 0)
        {
            StringBuffer sbSql = new StringBuffer("");
            sbSql.append(" WHERE ");
            for (Condition condition : lstCondition)
            {
                if (null == condition.getLinkFlag())
                {
                    condition.setLinkFlag(COConstants.SQL_LINK_AND);
                }
                sbSql.append(" ").append(condition.getName()).append(" ").append(condition.getOperator()).append(" ").append(getSqlValueByType(condition.getValue())).append(condition.getLinkFlag());
            }
            sbSql = sbSql.replace(sbSql.lastIndexOf(lstCondition.get(lstCondition.size() - 1).getLinkFlag()), sbSql.lastIndexOf(lstCondition.get(lstCondition.size() - 1).getLinkFlag()) + 5, " ");
            return sbSql.toString();
        }
        return null;
    }

    /**
     * buildInsertSql
     * @Description:
     * @param sourceEntity
     * @return
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static String buildInsertSql(BaseEntity sourceEntity) throws IllegalArgumentException, IllegalAccessException
    {
        if (null == sourceEntity)
        {
            return null;
        }
        StringBuffer sbSql = new StringBuffer("INSERT INTO ");
        TableAnnotation tableAnnotation = sourceEntity.getClass().getAnnotation(TableAnnotation.class);
        logger.debug("table name: " + tableAnnotation.table());
        sbSql.append(tableAnnotation.table()).append(" ");

        StringBuffer sbName = new StringBuffer("( ");
        StringBuffer sbValue = new StringBuffer("( ");

        Field[] classField = sourceEntity.getClass().getDeclaredFields();
        for (int i = 0; classField != null && i < classField.length; i++)
        {
            Field field = classField[i];
            field.setAccessible(true);
            TableAnnotation columnAnnotation = field.getAnnotation(TableAnnotation.class);
            if (null != columnAnnotation && columnAnnotation.isForSave())
            {
                sbName.append(field.getName()).append(", ");
                //根据不同的数据类型设定不同的值
                String tableName = tableAnnotation.table();
                String fieldName = field.getName();
                Object fieldValue = field.get(sourceEntity);
                //Object sqlValue = getSqlValueByType(field.get(sourceEntity));
                Object sqlValue = getSqlValueByType(tableName, fieldName, fieldValue);
                sbValue.append(sqlValue).append(", ");
            }
        }
        if (sbName.indexOf(",") != -1)
        {
            sbName = sbName.replace(sbName.lastIndexOf(","), sbName.lastIndexOf(",") + 1, ")");
        }
        if (sbValue.indexOf(",") != -1)
        {
            sbValue = sbValue.replace(sbValue.lastIndexOf(","), sbValue.lastIndexOf(",") + 1, ")");
        }
        sbSql.append(sbName).append(" VALUES ").append(sbValue);
        logger.debug("insert sql: " + sbSql.toString());
        return sbSql.toString();
    }

    /**
     * appendCondition
     * @Description:
     * @param conditionMap
     * @param sourceEntity
     * @return
     */
    public static Map appendCondition(Map conditionMap, BaseEntity sourceEntity)
    {
        try
        {
            if (null == sourceEntity)
            {
                return conditionMap;
            }
            Field[] classField = sourceEntity.getClass().getDeclaredFields();
            Field[] supperClassField = sourceEntity.getClass().getSuperclass().getDeclaredFields();
            /**
             * 设置本类属性为查询条件
             */
            for (int i = 0; classField != null && i < classField.length; i++)
            {
                Field field = classField[i];
                field.setAccessible(true);

                //根据不同的数据类型设定不同的值
                Object sqlValue = field.get(sourceEntity);
                if (sqlValue instanceof String)
                {
                    if (null != sqlValue && !"".equals(sqlValue))
                    {
                        sqlValue = ((String) sqlValue).trim();
                        conditionMap.put(field.getName(), sqlValue);
                    }
                }
                else
                {
                    if (null != sqlValue)
                    {
                        conditionMap.put(field.getName(), sqlValue);
                    }
                }
            }
            /**
             * 设置父类属性为查询条件
             */
            for (int i = 0; supperClassField != null && i < supperClassField.length; i++)
            {
                Field field = supperClassField[i];
                field.setAccessible(true);
                //根据不同的数据类型设定不同的值
                Object sqlValue = field.get(sourceEntity);
                if (sqlValue instanceof String)
                {
                    if (null != sqlValue && !"".equals(sqlValue))
                    {
                        sqlValue = ((String) sqlValue).trim();
                        conditionMap.put(field.getName(), sqlValue);
                    }
                }
                else
                {
                    if (null != sqlValue)
                    {
                        conditionMap.put(field.getName(), sqlValue);
                    }
                }
            }
            return conditionMap;
        }
        catch (Exception e)
        {
            logger.error("异常信息", e);
            return conditionMap;
        }
    }

    /**
     * getSqlValueByType
     * @Description:
     * @param fieldValue
     * @return
     */
    private static Object getSqlValueByType(Object fieldValue)
    {
        String returnValue = null;
        if (fieldValue instanceof String && null != fieldValue && !"".equals(fieldValue))
        {
            returnValue = "'" + fieldValue + "'";
        }
        else if (fieldValue instanceof Integer || fieldValue instanceof Long)
        {
            return fieldValue;
        }
        else if (fieldValue instanceof Date && null != fieldValue)
        {
            Date sourceDate = (Date) fieldValue;
            returnValue = "TO_DATE('" + DateUtils.convertToTime(sourceDate) + "','yyyy-mm-dd hh24:mi:ss')";
        }
        return returnValue;
    }

    /**
     * getSqlValueByType
     * @Description:
     * @param tableName
     * @param fieldName
     * @param fieldValue
     * @return
     */
    private static Object getSqlValueByType(String tableName, String fieldName, Object fieldValue)
    {
        String returnValue = null;
        if (fieldValue instanceof String && null != fieldValue && !"".equals(fieldValue))
        {
            if (("SEND_DOCUMENT".equals(tableName) && "doc_content".equals(fieldName)) || ("SEND_DOCUMENT".equals(tableName) && "remark".equals(fieldName)) || ("accept_document".equals(tableName) && "doc_content".equals(fieldName)) || ("accept_document".equals(tableName) && "remark".equals(fieldName)) || ("ACCEPT_DOCUMENT".equals(tableName) && "doc_content".equals(fieldName)) || ("ACCEPT_DOCUMENT".equals(tableName) && "remark".equals(fieldName)) || ("SHARE_INFORMATION".equals(tableName) && "doc_content".equals(fieldName)) || ("SHARE_INFORMATION".equals(tableName) && "remark".equals(fieldName)) || ("conference_info".equals(tableName) && "remark".equals(fieldName)) || ("conference_info".equals(tableName) && "agenda".equals(fieldName))
                    || ("conference_info".equals(tableName) && "content".equals(fieldName)))
            {
                returnValue = "";
                fieldValue = fieldValue.toString().replaceAll("'", "''");
                int len = fieldValue.toString().length();
                int t = (int) Math.ceil((double) len / (double) 2000);
                String strValue = fieldValue.toString();
                for (int i = 0; i < t; i++)
                {
                    if (i != (t - 1))
                    {
                        returnValue = returnValue + "to_clob('" + strValue.substring(i * 2000, (i + 1) * 2000) + "')||";
                    }
                    else
                    {
                        returnValue = returnValue + "to_clob('" + strValue.substring((t - 1) * 2000, len) + "')";
                    }
                }
            }
            else
            {
                fieldValue = fieldValue.toString().replaceAll("'", "''");
                returnValue = "'" + fieldValue + "'";
            }
        }
        else if (fieldValue instanceof Integer || fieldValue instanceof Long)
        {
            return fieldValue;
        }
        else if (fieldValue instanceof Date && null != fieldValue)
        {
            Date sourceDate = (Date) fieldValue;
            returnValue = "TO_DATE('" + DateUtils.convertToTime(sourceDate) + "','yyyy-mm-dd hh24:mi:ss')";
        }
        else if (fieldValue instanceof byte[] && null != fieldValue)
        {
            returnValue = "rawtohex('" + new String((byte[]) fieldValue) + "')";
        }
        return returnValue;
    }

    /**
     * buildCheckNameDuplicationSql
     * @Description:
     * @param targetClass
     * @param inputValue
     * @param originalityValue
     * @return
     * @throws SecurityException
     * @throws NoSuchFieldException
     */
    public static String buildCheckNameDuplicationSql(Class targetClass, String inputValue, String originalityValue) throws SecurityException, NoSuchFieldException
    {
        StringBuffer sbSql = new StringBuffer("SELECT count(id) FROM ");
        TableAnnotation tableAnnotation = (TableAnnotation) targetClass.getAnnotation(TableAnnotation.class);
        logger.debug("table name: " + tableAnnotation.table());
        sbSql.append(tableAnnotation.table()).append(" WHERE ");
        Field[] classField = targetClass.getDeclaredFields();
        for (int i = 0; classField != null && i < classField.length; i++)
        {
            Field field = classField[i];
            field.setAccessible(true);
            TableAnnotation fieldAnnotation = (TableAnnotation) field.getAnnotation(TableAnnotation.class);

            if (null != fieldAnnotation && fieldAnnotation.isCode())
            {
                sbSql.append(field.getName()).append(" = ").append(getSqlValueByType(inputValue.replaceAll("'", "''")));
                break;
            }
        }

        if (null != targetClass.getDeclaredField("is_delete"))
        {
            sbSql.append(COConstants.SQL_LINK_AND).append("is_delete = 0");
        }
        if (null != originalityValue && !"".equals(originalityValue))
        {
            sbSql.append(COConstants.SQL_LINK_AND).append("username != '").append(originalityValue.replaceAll("'", "''")).append("' ");
        }
        logger.debug("buildCheckNameDuplicationSql: " + sbSql.toString());
        return sbSql.toString();
    }

    /**
     * buildUserSelectConditon
     * @Description:
     * @param xmlSelectedCompany
     * @param include
     * @param org_id
     * @return
     * @throws DocumentException
     */
    public static String buildUserSelectConditon(String xmlSelectedCompany, boolean include, String org_id) throws DocumentException
    {
        StringBuffer sbUser = new StringBuffer("");
        Element root = XmlDomUtil.getRootElement(xmlSelectedCompany);
        if (null != root)
        {
            List<Element> lstElement = root.elements();
            if (include)
            {
                if (lstElement.size() > 0)
                {
                    sbUser.append(" where a.id in ( ");
                    for (Element e : lstElement)
                    {
                        sbUser.append("'").append(e.attributeValue("id")).append("',");
                    }
                    logger.debug(sbUser.toString());
                    sbUser.replace(sbUser.length() - 1, sbUser.length(), ")");
                }
            }
            else
            {
                if (lstElement.size() > 0)
                {
                    sbUser.append(" where a.id not in ( ");
                    for (Element e : lstElement)
                    {
                        sbUser.append("'").append(e.attributeValue("id")).append("',");
                    }
                    logger.debug(sbUser.toString());
                    sbUser.replace(sbUser.length() - 1, sbUser.length(), ")");
                }
            }
            //  Modify By Wangxm : 2010.02.22
            //sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.org_level='A' and a.username!='admin'");
            sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.id='");
            sbUser.append(org_id);
            sbUser.append("' and a.username!='admin'");
        }
        else
        {
            if (include)
            {
                sbUser.append(" where 1!=1");
            }
            else
            {
                //  Modify By Wangxm : 2010.02.22
                //sbUser.append(" where a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.org_level='A' and a.username!='admin'");
                sbUser.append(" where a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.id='");
                sbUser.append(org_id);
                sbUser.append("' and a.username!='admin'");
            }

        }

        logger.debug(sbUser.toString());
        return sbUser.toString();
    }

    /**
     * buildHandleUserSelectConditon
     * @Description:
     * @param xmlSelectedCompany
     * @param include
     * @param org_id
     * @return
     * @throws DocumentException
     */
    public static String buildHandleUserSelectConditon(String xmlSelectedCompany, boolean include, String org_id) throws DocumentException
    {
        StringBuffer sbUser = new StringBuffer(" where 1=1 ");
        Element root = XmlDomUtil.getRootElement(xmlSelectedCompany);
        if (null != root)
        {
            List<Element> lstElement = root.elements();
            if (include)
            {
                if (lstElement.size() > 0)
                {
                    sbUser.append(" and a.id in ( ");
                    for (Element e : lstElement)
                    {
                        sbUser.append("'").append(e.attributeValue("id")).append("',");
                    }
                    logger.debug(sbUser.toString());
                    sbUser.replace(sbUser.length() - 1, sbUser.length(), ")");
                }
            }
            else
            {
                if (lstElement.size() > 0)
                {
                    sbUser.append(" and a.id not in ( ");
                    for (Element e : lstElement)
                    {
                        sbUser.append("'").append(e.attributeValue("id")).append("',");
                    }
                    logger.debug(sbUser.toString());
                    sbUser.replace(sbUser.length() - 1, sbUser.length(), ")");
                }
            }
            //sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.org_level='A' and a.username!='admin' and c.role_id='3'");
            sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.id='");
            sbUser.append(org_id);
            sbUser.append("' and a.username!='admin'");
        }
        else
        {
            if (include)
            {
                sbUser.append(" and 1!=1");
            }
            else
            {
                //sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.org_level='A' and a.username!='admin' and c.role_id='3' ");
                sbUser.append(" and a.is_delete='0' and a.is_active='1' and a.org_id=b.id and b.id='");
                sbUser.append(org_id);
                sbUser.append("' and a.username!='admin'");
            }

        }

        logger.debug(sbUser.toString());
        return sbUser.toString();
    }

    /**
     * buildCompanySelectConditon
     * @Description:
     * @param flag
     * @param xmlSelectedCompany
     * @param include
     * @param companyType
     * @return
     * @throws DocumentException
     */
    public static String buildCompanySelectConditon(String flag, String xmlSelectedCompany, boolean include, String companyType) throws DocumentException
    {
        StringBuffer sbCompany = new StringBuffer("");
        Element root = XmlDomUtil.getRootElement(xmlSelectedCompany);
        if ("null".equals(companyType))
        {
            if (null != root)
            {
                List<Element> lstElement = root.elements();
                if (include)
                {
                    if (lstElement.size() > 0)
                    {

                        sbCompany.append(" WHERE is_delete=0 AND in_use=1 ").append(" AND ( ");
                        for (Element e : lstElement)
                        {
                            if (e.attributeValue("send_type").equals(flag))
                            {
                                sbCompany.append(" id= ").append(getSqlValueByType(e.attributeValue("id"))).append(COConstants.SQL_LINK_OR);
                            }
                        }
                        if (sbCompany.indexOf(COConstants.SQL_LINK_OR) != -1)
                        {
                            sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_OR), sbCompany.length() - 1, " )");
                        }
                        else
                        {
                            sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_AND), sbCompany.length() - 1, " and id is null ");
                        }
                    }
                }
                else
                {
                    if (lstElement.size() > 0)
                    {
                        sbCompany.append(" WHERE is_delete=0 AND in_use=1 ").append(" AND ");

                        for (Element e : lstElement)
                        {
                            sbCompany.append(" id!= ").append(getSqlValueByType(e.attributeValue("id"))).append(COConstants.SQL_LINK_AND);
                        }
                        sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_AND), sbCompany.length() - 1, " ");

                    }
                }
            }
            else
            {
                if (!include)
                {
                    sbCompany.append(" WHERE is_delete=0 AND in_use=1 ");
                }
            }
        }
        else
        {
            if (null != root)
            {
                List<Element> lstElement = root.elements();
                if (include)
                {
                    if (lstElement.size() > 0)
                    {

                        //sbCompany.append(" WHERE is_delete=0 AND in_use=1 AND org_level=").append(getSqlValueByType(companyType)).append(" AND ( ") ;
                        sbCompany.append(" WHERE is_delete=0 AND in_use=1 ").append(" AND ( ");
                        for (Element e : lstElement)
                        {
                            if (e.attributeValue("send_type").equals(flag))
                            {
                                sbCompany.append(" id= ").append(getSqlValueByType(e.attributeValue("id"))).append(COConstants.SQL_LINK_OR);
                            }
                        }
                        if (sbCompany.indexOf(COConstants.SQL_LINK_OR) != -1)
                        {
                            sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_OR), sbCompany.length() - 1, " )");
                        }
                        else
                        {
                            sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_AND), sbCompany.length() - 1, " and id is null ");
                        }
                    }
                }
                else
                {
                    if (lstElement.size() > 0)
                    {
                        //sbCompany.append(" WHERE is_delete=0 AND in_use=1 AND org_level=").append(getSqlValueByType(companyType)).append(" AND ") ;
                        sbCompany.append(" WHERE is_delete=0 AND in_use=1 ").append(" AND ");
                        for (Element e : lstElement)
                        {
                            sbCompany.append(" id!= ").append(getSqlValueByType(e.attributeValue("id"))).append(COConstants.SQL_LINK_AND);
                        }
                        sbCompany = sbCompany.replace(sbCompany.lastIndexOf(COConstants.SQL_LINK_AND), sbCompany.length() - 1, " ");
                        String orgId = SecurityContextInfo.getSessionEntity().getUserEntity().getOrg_id();
                        sbCompany.append(" AND parentid = ").append(getSqlValueByType(orgId));
                    }
                }
            }
            else
            {
                if (!include)
                {
                    //sbCompany.append(" WHERE is_delete=0 AND in_use=1 AND org_level=").append(getSqlValueByType(companyType));
                    sbCompany.append(" WHERE is_delete=0 AND in_use=1 ");
                    String orgId = SecurityContextInfo.getSessionEntity().getUserEntity().getOrg_id();
                    sbCompany.append(" AND parentid = ").append(getSqlValueByType(orgId));
                }
            }
        }
        logger.debug(sbCompany.toString());
        return sbCompany.toString();
    }

    /**
     * buildDeleteSql
     * @Description:
     * @param sourceEntity
     * @return
     * @throws IllegalArgumentException
     * @throws SecurityException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public static String buildDeleteSql(BaseEntity sourceEntity) throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException
    {
        if (null == sourceEntity)
        {
            return null;
        }
        StringBuffer sbSql = new StringBuffer("DELETE FROM ");
        TableAnnotation tableAnnotation = sourceEntity.getClass().getAnnotation(TableAnnotation.class);
        logger.debug("table name: " + tableAnnotation.table());
        sbSql.append(tableAnnotation.table());
        sbSql.append(" WHERE id= ").append("'").append(sourceEntity.getClass().getMethod("getId").invoke(sourceEntity)).append("'");
        logger.debug(sbSql.toString());
        return sbSql.toString();
    }

    /**
     * Set_OracleChar
     * @Description:
     * @param strObj
     * @return
     */
    public static String Set_OracleChar(String strObj)
    {
        if (strObj != null)
        {
            //  去空格
            strObj = strObj.trim();
        }
        else
        {
            return null;
        }

        if (strObj.length() == 0)
        {
            return "";
        }
        //  替换特殊字符
        return strObj.replace("\\", "\\\\").replace("%", "\\%").replace("％", "\\％").replace("_", "\\_").replace("＿", "\\＿").replace("'", "''");
    }

}
